﻿Public Class perCep
    Inherits PersistenciaPadrao.AcessoBD

#Region "Transação"

    Public Sub configurarAberturaConexao()

        If perConexao.SqlComando Is Nothing OrElse perConexao.SqlConexao Is Nothing Then
            MyBase.abrirConexao()
            perConexao.SqlComando = MyBase.retornarComandoSqlServer
            perConexao.SqlConexao = MyBase.retornarConexaoSqlServer
        Else
            MyBase.configurarTransacaoSqlServer(perConexao.SqlComando, perConexao.SqlConexao)
        End If

    End Sub

    Public Sub configurarFechamentoConexao()
        MyBase.fecharConexao()
        perConexao.SqlComando = Nothing
        perConexao.SqlConexao = Nothing
    End Sub

#End Region

    Public Function selecionarEnderecoPorCep(ByVal sCep As String) As DataRow
        Dim sSql As String
        Dim dtbEndereco As DataTable

        sSql = "SELECT * " & Environment.NewLine &
               "  FROM endereco_completo" & Environment.NewLine &
               " WHERE cep=@cep"

        MyBase.limparParametros()
        MyBase.inserirParametro("@cep", sCep)

        dtbEndereco = MyBase.executarConsulta(sSql, True)

        If dtbEndereco.Rows.Count > 0 Then
            Return dtbEndereco.Rows(0)
        Else
            Return Nothing
        End If

    End Function

    Public Sub inserirLogradouroCep(ByVal iCep As Int32, ByVal iCodLogradouro As Int32, ByVal bAbrirFechar As Boolean)
        Dim sSql As String

        If Not bAbrirFechar Then
            Me.configurarAberturaConexao()
        End If

        sSql = "INSERT INTO end_logradouro_cep" & Environment.NewLine &
               "            (" & Environment.NewLine &
               "            CodLogradouro " & Environment.NewLine &
               "            ,Cep" & Environment.NewLine &
               "            )" & Environment.NewLine &
               "      VALUES" & Environment.NewLine &
               "           (" & Environment.NewLine &
               "      @CodLogradouro" & Environment.NewLine &
               "     ,@Cep" & Environment.NewLine &
               "           )" & Environment.NewLine

        MyBase.limparParametros()
        MyBase.inserirParametro("@codLogradouro", iCodLogradouro)
        MyBase.inserirParametro("@cep", iCep)

        MyBase.executarAcao(sSql, bAbrirFechar)

    End Sub

    Public Sub excluirCepPorLogradouro(ByVal iCodLogradouro As Int32, ByVal bAbrirFechar As Boolean)
        Dim sSql As String

        If Not bAbrirFechar Then
            Me.configurarAberturaConexao()
        End If

        sSql = "DELETE FROM end_logradouro_cep" & Environment.NewLine &
               "       WHERE codLogradouro = @CodLogradouro" & Environment.NewLine

        MyBase.limparParametros()
        MyBase.inserirParametro("@codLogradouro", iCodLogradouro)

        MyBase.executarAcao(sSql, bAbrirFechar)

    End Sub


End Class
